
"""
Copyright (c) 2023 - present SKbioinfo
"""

from flask_restful import Resource, reqparse
from flask import request,jsonify
from flask_login import login_required,current_user
from apps.NGS_API.utils.log_helper import lg
from apps.NGS_API.connector.db_common import *
from apps.NGS_API.common.common_time import *
from apps.NGS_API.common.db_table_config import *

class interfaceTableSamplesearch(Resource):
    @login_required
    def post(self):
        try:
            parser = reqparse.RequestParser()
            parser.add_argument("title",type=str,required=True,help="标题输入错误！")
            parser.add_argument("search_info",type=str,required=True,help="检索信息错误！")
            args = parser.parse_args()
            mapping = {
                "系统样品编号":"sam.sample_id",
                "系统项目编号":"sam.project_id",
                "项目编号":"sam.primary_project_id",
                "样品编号":"sam.primary_sample_id"
            }
            cols = '''
                sam.id,
                sam.sample_id,
                sam.primary_project_id,
                sam.primary_sample_id,
                proj.project_id,
                sam.operate_user,
                sam.check_user,
                sam.sample_source,
                sam.sample_type,
                sam.sample_name,
                sam.init_amount,
                sam.cur_amount,
                sam.create_time,
                sam.modify_time,
                sam_stc.cur_amount,
                sam_stc.status
            '''
            sample_stock_sql = '''
                SELECT
                  {cols}
                FROM {table_one} as proj
                LEFT JOIN {table_two} as sam
                ON proj.project_id = sam.project_id
                LEFT JOIN {table_three} as sam_stc
                ON sam.sample_id = sam_stc.sample_id
                WHERE {query_col} = \"{query_value}\"
            '''.format(
                cols = cols,
                table_one = project_db["name"],
                table_two = sample_db["name"],
                table_three = sample_stock_db["name"],
                query_col = mapping[args.get('title')],
                query_value = args.get('search_info')
            )
            sample_results = dic_query_info(sample_stock_sql)
            sample_infos = []
            if len(sample_results) == 0:
                pass
            else:
                status_mapping = {
                    None:"NA",
                    1:"入库",
                    -1:"出库",
                    0:"未入库"
                }
                for sample in sample_results:
                    instock_amount = 0
                    if sample["sam_stc.cur_amount"] == None:
                        pass
                    else:
                        instock_amount = sample["sam_stc.cur_amount"]
                        sample_infos.append({
                            "id":sample["id"],
                            "pri_proj_id":sample["primary_project_id"],
                            "pri_sam_id":sample["primary_sample_id"],
                            "sam_id":sample["sample_id"],
                            "proj_id":sample["project_id"],
                            "op_user":sample["operate_user"],
                            "ch_user":sample["check_user"],
                            "sam_source":sample["sample_source"],
                            "sam_type":sample["sample_type"],
                            "sam_name":sample["sample_name"],
                            "init_amount":sample["init_amount"],
                            "cur_amount":sample["cur_amount"],
                            "cre_time": sample["create_time"],
                            "md_time": sample["modify_time"],
                            "istc_amount":instock_amount,
                            "status": status_mapping[sample["status"]],
                            "istc_user": current_user.username,
                            "istc_ch_user": current_user.username,
                            "flag":False
                        })
            response = jsonify(sample_infos)
            response.status = 202
            return response
        except Exception as e:
            lg.error(e)
        
class interfaceTablesearch(Resource):
    @login_required
    def post(self):
        try:
            parser = reqparse.RequestParser()
            parser.add_argument("title",type=str,required=True,help="标题输入错误！")
            parser.add_argument("search_info",type=str,required=True,help="检索信息错误！")
            args = parser.parse_args()
            ### SQL: search title infos ###
            cols = '''
                tb1.primary_id,
                tb2.primary_sample_id,
                tb3.extraction_id,
                tb4.library_id,
                tb2.init_amount,
                tb5.cur_amount,
                tb2.unit
            '''
            query_info_sql = '''
                SELECT 
                    {cols}
                FROM {table_one} as tb1
                LEFT JOIN 
                    {table_two} as tb2
                ON tb1.{col_one} = tb2.{col_one}
                LEFT JOIN
                    {table_three} as tb3
                ON tb2.{col_two} = tb3.{col_two}
                LEFT JOIN
                    {table_four} as tb4
                ON tb3.{col_three} = tb4.{col_three}
                LEFT JOIN
                    {table_five} as tb5
                ON tb2.{col_four} = tb5.{col_four}
            '''.format(
                cols = cols,
                table_one = project_db["name"],
                col_one = "project_id",
                table_two = sample_db["name"],
                col_two = "sample_id",
                table_three = extraction_db["name"],
                col_three = "stock_id",
                table_four = ngsLib_db["name"],
                col_four = "sample_id",
                table_five = sample_stock_db["name"]
            )
            infos = {}
            table_infos=[]
            if args.get("search_info") == "":
                table_infos = [{
                    "proj_id": "NA",
                    "sam_id": "NA",
                    "ext_id": "NA",
                    "lib_id": "NA",
                    "init_amount": "NA",
                    "cur_amount": "NA",
                    "flag": False
                }]
                infos["table_infos"] = table_infos
                response=jsonify(infos)
                response.status_code = 202
            else:
                ### mapping name ###
                mapping_name = {
                    "项目编号": "tb1.project_id",
                    "样品编号": "tb2.sample_id",
                    "抽提编号": "tb3.extraction_id",
                    "文库编号": "tb4.library_id"
                }
                query_col = mapping_name[args.get('title')]
                query_info_sql = '''
                    {previous_sql}
                    WHERE {query_col} = \"{query_value}\"
                '''.format(
                    previous_sql = query_info_sql,
                    query_col = query_col,
                    query_value = args.get("search_info")
                )
                ### SQL: table query infos ###
                table_query_infos = query_info(query_info_sql)
                for table_ids in table_query_infos:
                    table_infos.append({
                        "proj_id": table_ids[0],
                        "sam_id": table_ids[1],
                        "ext_id": table_ids[2],
                        "lib_id": table_ids[3],
                        "init_amount": table_ids[4],
                        "cur_amount": table_ids[5],
                        "unit": table_ids[6].split(",")[1],
                        "flag": False
                    })
                infos["table_infos"] = table_infos
            response=jsonify(infos)
            response.status_code = 202
            return response
        except Exception as e:
            lg.error(e)